package com.qdlc.p2p.dal.dao.impl;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import javax.persistence.Query;

import org.apache.log4j.Logger;
import org.springframework.stereotype.Repository;

import com.qdlc.p2p.common.constant.Global;
import com.qdlc.p2p.common.exception.AccountException;
import com.qdlc.p2p.common.util.BeanUtil;
import com.qdlc.p2p.common.util.DateUtil;
import com.qdlc.p2p.common.util.StringUtil;
import com.qdlc.p2p.dal.common.dao.jpa.BaseDaoImpl;
import com.qdlc.p2p.dal.common.model.jpa.OrderFilter.OrderType;
import com.qdlc.p2p.dal.common.model.jpa.PageDataList;
import com.qdlc.p2p.dal.common.model.jpa.QueryParam;
import com.qdlc.p2p.dal.common.model.jpa.SearchFilter;
import com.qdlc.p2p.dal.common.model.jpa.SearchFilter.Operators;
import com.qdlc.p2p.dal.dao.AccountRechargeDao;
import com.qdlc.p2p.dal.dao.VerifyLogDao;
import com.qdlc.p2p.dal.dto.AccountRecharge;
import com.qdlc.p2p.dal.dto.PayOfflinebank;
import com.qdlc.p2p.dal.dto.VerifyLog;
import com.qdlc.p2p.dal.model.AccountRechargeModel;

@Repository("accountRechargeDao")
public class AccountRechargeDaoImpl extends BaseDaoImpl<AccountRecharge> implements AccountRechargeDao {
	private final static Logger logger = Logger.getLogger(AccountRechargeDaoImpl.class);
	
	@Override
	public AccountRecharge getRechargeByTradeno(String tradeNo) {
		QueryParam param = QueryParam.getInstance();
		param.addParam("tradeNo", tradeNo);
		return findByCriteriaForUnique(param);
	}

	@Override
	public void updateRechargeFee(double fee, long id) {
		updateByJpql("UPDATE AccountRecharge SET fee=:fee where id=:id", new String[]{"fee", "id"}, new Object[]{fee, id});
	}

	@Override
	public AccountRechargeModel getRechargeSummary(long userId) {
		AccountRechargeModel model = new AccountRechargeModel();
		String rechargeTotalSql = "SELECT SUM(money) AS num FROM " + Global.DB_PREFIX + "account_recharge WHERE status=1 AND money > 0 AND user_id = :userId";
		Query query = em.createNativeQuery(rechargeTotalSql).setParameter("userId", userId);
		Object totalObj = query.getSingleResult();
		if (totalObj != null) {
			double rechargeTotal = Double.parseDouble(query.getSingleResult().toString());
			model.setRechargeTotal(rechargeTotal);
		}
		String onlineRechargeTotalSql = "SELECT SUM(money) AS num FROM " + Global.DB_PREFIX + "account_recharge WHERE status=1 AND (type=1 OR type=2) AND user_id = :userId";
		Query onlineQuery = em.createNativeQuery(onlineRechargeTotalSql).setParameter("userId", userId);
		Object onlineObj = onlineQuery.getSingleResult();
		if (onlineObj != null) {
			double onlineRechargeTotal = Double.parseDouble(onlineQuery.getSingleResult().toString());
			model.setOnlineRechargeTotal(onlineRechargeTotal);
		}
		String offlineRechargeTotalSql = "SELECT SUM(money) AS num FROM " + Global.DB_PREFIX + "account_recharge WHERE status=1 AND type=3 AND user_id = :userId";
		Query offlineQuery = em.createNativeQuery(offlineRechargeTotalSql).setParameter("userId", userId);
		Object offlineObj = offlineQuery.getSingleResult();
		if (offlineObj != null) {
			double offlineRechargeTotal = Double.parseDouble(offlineQuery.getSingleResult().toString());
			model.setOfflineRechargeTotal(offlineRechargeTotal);
		}
		return model;
	}

	@Override
	public int count(int status) {
		QueryParam param = QueryParam.getInstance().addParam("status", 0);
		return countByCriteria(param);
	}

    @Override
    public void updateStatus(long id, int status) {
        String sql = "UPDATE AccountRecharge SET status = :status WHERE id = :id ";
        Query query = em.createQuery(sql);
        query.setParameter("status", status);
        query.setParameter("id", id);
        int result = query.executeUpdate();
        if (result != 1) {
            throw new AccountException("更改充值记录状态失败！");
        }
    }
    
    @Override
    public int rechargedUserCount() {
        String sql = "SELECT COUNT(DISTINCT t.user_id) FROM " + Global.DB_PREFIX + "account_recharge t";
        Query query = em.createNativeQuery(sql);
        Object count = query.getSingleResult();
        if(count != null){
            return Integer.parseInt(count.toString());
        }
        return 0;        
    }

    @Override
    public double rechargedAllMoney() {
        String sql = "SELECT SUM(money) FROM " + Global.DB_PREFIX + "account_recharge WHERE status = 1";
        Query query = em.createNativeQuery(sql);
        Object obj = query.getSingleResult();
        if (obj != null) {
            return Double.parseDouble(obj.toString());
        }
        return 0;
    }

	@Override
	public PageDataList<AccountRechargeModel> list(long userId, AccountRechargeModel model) {
		QueryParam param = QueryParam.getInstance();
		if (model != null) {
			if (StringUtil.isNotBlank(model.getStartTime())) {
				Date start = DateUtil.valueOf(model.getStartTime() + " 00:00:00");
				param.addParam("addTime", Operators.GTE, start);
			}
			Date nowdate = DateUtil.getDate(System.currentTimeMillis() / 1000 + "");
			if (model.getTime() == 7) {
				param.addParam("addTime", Operators.GTE, DateUtil.rollDay(nowdate, -7));
				param.addParam("addTime", Operators.LTE, nowdate);
			} else if (model.getTime() > 0 && model.getTime() < 4) {
				param.addParam("addTime", Operators.GTE, DateUtil.rollMon(nowdate, -model.getTime()));
				param.addParam("addTime", Operators.LTE, nowdate);
			}
			if (StringUtil.isNotBlank(model.getEndTime())) {
				Date end = DateUtil.valueOf(model.getEndTime() + " 23:59:59");
				param.addParam("addTime", Operators.LTE, end);
			}
			if (model.getStatus() != 99) {
				param.addParam("status", model.getStatus());
			}
		}
		param.addParam("user.userId", userId);
		param.addPage(model.getPage());
		param.addOrder(OrderType.DESC, "addTime");
		PageDataList<AccountRecharge> pageDateList = this.findPageList(param);
		PageDataList<AccountRechargeModel> pageDateList_ = new PageDataList<AccountRechargeModel>();
		List<AccountRechargeModel> list = new ArrayList<AccountRechargeModel>();
		pageDateList_.setPage(pageDateList.getPage());
		VerifyLogDao verifyLogDao = (VerifyLogDao) BeanUtil.getBean("verifyLogDao");
		if (pageDateList.getList().size() > 0) {
			for (int i = 0; i < pageDateList.getList().size(); i++) {
				AccountRecharge recharge = (AccountRecharge) pageDateList.getList().get(i);
				AccountRechargeModel arm = AccountRechargeModel.instance(recharge);
				VerifyLog verifyLog = verifyLogDao.findByType(recharge.getId(), "verifyAccountRecharge", 1);
				String remark = (verifyLog != null) ? verifyLog.getRemark() : "";
				arm.setVerifyRemark(remark);
				arm.setAddTimeStr(DateUtil.dateStr4(recharge.getAddTime()));
				list.add(arm);
			}
		}
		pageDateList_.setList(list);
		return pageDateList_;
	}

	@Override
	public PageDataList<AccountRechargeModel> list(AccountRechargeModel model) {
		QueryParam param = QueryParam.getInstance();
		if (model != null && !StringUtil.isBlank(model.getSearchName())) {// 模糊条件查询
			SearchFilter orFilter1 = new SearchFilter("user.userName", Operators.LIKE, model.getSearchName().trim());
			SearchFilter orFilter2 = new SearchFilter("user.realName", Operators.LIKE, model.getSearchName().trim());
			param.addOrFilter(orFilter1, orFilter2);
		} else {// 精确条件查询
			if (StringUtil.isNotBlank(model.getTradeNo())) {
				param.addParam("tradeNo", Operators.LIKE, model.getTradeNo().trim());
			}
			if (StringUtil.isNotBlank(model.getUserName())) {
				param.addParam("user.userName", Operators.LIKE, model.getUserName().trim());
			}
			if (StringUtil.isNotBlank(model.getRealName())) {
				param.addParam("user.realName", Operators.LIKE, model.getRealName().trim());
			}
			if(model.getType() != 99){
				param.addParam("type", model.getType());
			}
			if(model.getStatus() != 99){
				param.addParam("status", model.getStatus());
			}
			if (StringUtil.isNotBlank(model.getStartTime())) {
				Date start = DateUtil.valueOf(model.getStartTime() + " 00:00:00");
				param.addParam("addTime", Operators.GTE, start);
			}
			if (StringUtil.isNotBlank(model.getEndTime())) {
				Date end = DateUtil.valueOf(model.getEndTime() + " 23:59:59");
				param.addParam("addTime", Operators.LTE, end);
			}
		}
		param.addPage(model.getPage(), model.getRows());
		if (model.getOrder().equals("desc")) {
			param.addOrder(OrderType.DESC, model.getSort());
		} else {
			param.addOrder(OrderType.ASC, model.getSort());
		}
		PageDataList<AccountRecharge> pageDateList = this.findPageList(param);
		PageDataList<AccountRechargeModel> pageDateList_ = new PageDataList<AccountRechargeModel>();
		List<AccountRechargeModel> list = new ArrayList<AccountRechargeModel>();
		pageDateList_.setPage(pageDateList.getPage());
		if (pageDateList.getList().size() > 0) {
			for (int i = 0; i < pageDateList.getList().size(); i++) {
				AccountRecharge recharge = (AccountRecharge) pageDateList.getList().get(i);
				AccountRechargeModel arm = AccountRechargeModel.instance(recharge);
				try {
					arm.setUserName(recharge.getUser().getUserName());
					arm.setRealName(recharge.getUser().getRealName());
					PayOfflinebank pay = recharge.getPayOfflinebank();
					if (pay != null) {
						arm.setPayOfflinebankInfo(pay.getBankNo() + " : " + pay.getBank() + pay.getBranch());
					}
					list.add(arm);
				} catch (Exception e) {
					logger.error(e);
				}
			}
		}
		pageDateList_.setList(list);
		return pageDateList_;
	}

	@Override
	public double todayRechargedAllMoney() {
		String sql = "SELECT SUM(money) FROM "+Global.DB_PREFIX+ "account_recharge recharge,"+Global.DB_PREFIX+"user_cache userCache WHERE recharge.status = 1 and recharge.add_time >=? AND userCache.user_id = recharge.user_id AND userCache.user_type=1";
		Query query = em.createNativeQuery(sql).setParameter(1, DateUtil.dateStr2(DateUtil.getNow()));
		Object obj = query.getSingleResult();
		if (obj != null) {
			return Double.parseDouble(obj.toString());
		}
		return 0;
	}

	@Override
	public double getRechargeAmountByDate(String date) {
		String dateFormat = "%Y-%m-%d";
		if (date.length() == 7) {
			dateFormat = "%Y-%m";
		}
		String sql = "select SUM(money) from " + Global.DB_PREFIX+"account_recharge recharge,"+Global.DB_PREFIX+"user_cache userCache where date_format(recharge.add_time,'" + dateFormat
				+ "') = ? AND recharge.status = 1 AND userCache.user_id = recharge.user_id AND userCache.user_type=1 ";
		Query query = em.createNativeQuery(sql).setParameter(1, date);
		Object obj = query.getSingleResult();
		double amount = 0;
		if (obj != null) {
			amount = Double.parseDouble(((obj.toString())));
		}
		return amount;
	}

	@Override
	public double rechargedAllMomeny() {
		String sql = "SELECT SUM(money) FROM "+ Global.DB_PREFIX+"account_recharge recharge,"+Global.DB_PREFIX+"user_cache userCache WHERE recharge.status = 1 AND userCache.user_id = recharge.user_id AND userCache.user_type=1";
		Query query = em.createNativeQuery(sql);
		Object obj = query.getSingleResult();
		if (obj != null) {
			return Double.parseDouble(obj.toString());
		}
		return 0;
	}
}
